﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using Microsoft.SqlServer.Server;

namespace ColorApplication.Helper
{
    public static class SQLHelper
    {
        public static SqlCommand GetCommand(SqlConnection connection, string sprocName, SqlParameter[] parameters)
        {
            //get connection string
            //Configuration rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/");
            //System.Configuration.ConnectionStringSettings connString = new ConnectionStringSettings();
            //if (rootWebConfig.ConnectionStrings.ConnectionStrings.Count > 0)
            //{
            //    connString =
            //        rootWebConfig.ConnectionStrings.ConnectionStrings["ApplicationServices"];
            //}

            connection.ConnectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;

            // call stored proc
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = sprocName;
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }
            return command;

        }

        public static int GetIntValue(object o)
        {
            var returnedValue = 0;
            if (!int.TryParse(o.ToString(), out returnedValue))
            {
                returnedValue = 0;
            }
            return returnedValue;
        }

        public static DateTime GetDateTimeValue(object o)
        {
            DateTime returnedValue;
            if (!DateTime.TryParse(o.ToString(), out returnedValue))
            {
                returnedValue = DateTime.MinValue;
            }
            return returnedValue;
        }

        public static decimal GetDecimalValue(object o)
        {
            decimal returnedValue;
            if (!decimal.TryParse(o.ToString(), out returnedValue))
            {
                returnedValue = -1;
            }
            return returnedValue;
        }

        public static bool GetBoolValue(object o)
        {
            bool returnedValue;
            var s = o.ToString();
            if (s == "1")
            {
                s = "True";
            } if (s == "0")
            {
                s = "False";
            }

            if (!bool.TryParse(s, out returnedValue))
            {
                returnedValue = false;
            }
            return returnedValue;
        }

        public static List<SqlDataRecord> GenerateDataRecords(List<int> listIds)
        {
            if (listIds == null || listIds.Count == 0)
                return null;

            var sqlMetaDatas = new SqlMetaData[1];
            sqlMetaDatas[0] = new SqlMetaData("ID", SqlDbType.Int);

            var dataRecords = new List<SqlDataRecord>();
            foreach (var listId in listIds)
            {
                var dataRecord = new SqlDataRecord(sqlMetaDatas);
                dataRecord.SetInt32(0, listId);
                dataRecords.Add(dataRecord);
            }
            return dataRecords;
        }
    }
}